package com.yyi.springcloud.utils;

import com.yyi.springcloud.entities.TestDataVo;
import org.apache.poi.ss.usermodel.*;

import java.io.File;
import java.io.FileInputStream;
import java.net.URL;
import java.util.List;

public class ExportExcelUtils_DataList_Mysql {

	/**
	 * 描述：根据文件路径获取项目中的文件
	 * 
	 * @param fileDir
	 *            文件路径
	 * @return
	 * @throws Exception
	 */
	public File getExcelDemoFile(String fileDir) throws Exception {
		String classDir = null;
		String fileBaseDir = null;
		File file = null;
		ClassLoader contextClassLoader = Thread.currentThread().getContextClassLoader();
		URL resource = contextClassLoader.getResource("");
		classDir = resource.getPath();
		// fileBaseDir = classDir.substring(0, classDir.lastIndexOf("classes"));
		fileBaseDir = classDir;

		file = new File(fileBaseDir + fileDir);
		if (!file.exists()) {
			throw new Exception("模板文件不存在！");
		}
		return file;
	}

	public Workbook writeNewExcel(File file, String sheetName, List<TestDataVo> list) throws Exception {
		Workbook wb = null;
		Row row = null;
		Cell cell = null;

		FileInputStream fis = new FileInputStream(file);
		wb = new ImportExcelUtil().getWorkbook(fis, file.getName()); // 获取工作薄
		Sheet sheet = wb.getSheet(sheetName);

		// 循环插入数据
		int lastRow = sheet.getLastRowNum() + 1; // 插入数据的数据ROW
		CellStyle cs = setSimpleCellStyle(wb); // Excel单元格样式
		for (int i = 0; i < list.size(); i++) {
			row = sheet.createRow(lastRow + i); // 创建新的ROW，用于数据插入

			// 按项目实际需求，在该处将对象数据插入到Excel中
			TestDataVo vo = list.get(i);
			if (null == vo) {
				break;
			}
			// Cell赋值开始
			// 序号
			// cell = row.createCell(0);
			// cell.setCellValue(i+1);
			// cell.setCellStyle(cs);
			// 日期
			cell = row.createCell(0);
			cell.setCellValue(vo.getDate() + " " + vo.getTime());
			cell.setCellStyle(cs);
			// 时间
			cell = row.createCell(1);
			cell.setCellValue(vo.getHowlong());
			cell.setCellStyle(cs);

			List<String> sensorValues = vo.getSensorValueList();
			// 环温1
			cell = row.createCell(2);
			cell.setCellValue("");
			cell.setCellStyle(cs);
			// TMP2
			cell = row.createCell(3);
			cell.setCellValue(sensorValues.get(1));
			cell.setCellStyle(cs);
			// TMP3
			cell = row.createCell(4);
			cell.setCellValue(sensorValues.get(2));
			cell.setCellStyle(cs);
			// TMP4
			cell = row.createCell(5);
			cell.setCellValue(sensorValues.get(3));
			cell.setCellStyle(cs);
			// TMP5
			cell = row.createCell(6);
			cell.setCellValue(sensorValues.get(4));
			cell.setCellStyle(cs);
			// TMP6
			cell = row.createCell(7);
			cell.setCellValue(sensorValues.get(5));
			cell.setCellStyle(cs);
			// TMP7
			cell = row.createCell(8);
			cell.setCellValue(sensorValues.get(6));
			cell.setCellStyle(cs);
			// M10
			cell = row.createCell(9);
			cell.setCellValue("");
			cell.setCellStyle(cs);
			// T1
			cell = row.createCell(10);
			cell.setCellValue("");
			cell.setCellStyle(cs);
			// T2
			cell = row.createCell(11);
			cell.setCellValue(sensorValues.get(8));
			cell.setCellStyle(cs);
			// T3
			cell = row.createCell(12);
			cell.setCellValue(sensorValues.get(9));
			cell.setCellStyle(cs);
			// TC2
			cell = row.createCell(13);
			cell.setCellValue("");
			cell.setCellStyle(cs);
			// TC3
			cell = row.createCell(14);
			cell.setCellValue("");
			cell.setCellStyle(cs);
			// M12
			cell = row.createCell(15);
			cell.setCellValue("");
			cell.setCellStyle(cs);
			// Ta1
			cell = row.createCell(16);
			cell.setCellValue(sensorValues.get(11));
			cell.setCellStyle(cs);
			// Ta2
			cell = row.createCell(17);
			cell.setCellValue(sensorValues.get(12));
			cell.setCellStyle(cs);
			// 电压
			cell = row.createCell(18);
			cell.setCellValue(sensorValues.get(13));
			cell.setCellStyle(cs);
			// 电流
			cell = row.createCell(19);
			cell.setCellValue(sensorValues.get(14));
			cell.setCellStyle(cs);
			// 功率
			cell = row.createCell(20);
			cell.setCellValue(sensorValues.get(15));
			cell.setCellStyle(cs);
			// 耗电量
			cell = row.createCell(21);
			cell.setCellValue(sensorValues.get(16));
			cell.setCellStyle(cs);
			// 频率
			cell = row.createCell(22);
			cell.setCellValue(sensorValues.get(17));
			cell.setCellStyle(cs);
			// 湿温
			cell = row.createCell(23);
			cell.setCellValue(sensorValues.get(18));
			cell.setCellStyle(cs);

		}
		return wb;
	}

	/**
	 * 描述：设置简单的Cell样式
	 * 
	 * @return
	 */
	public CellStyle setSimpleCellStyle(Workbook wb) {
		CellStyle cs = wb.createCellStyle();
		cs.setBorderBottom(CellStyle.BORDER_THIN); // 下边框
		cs.setBorderLeft(CellStyle.BORDER_THIN);// 左边框
		cs.setBorderTop(CellStyle.BORDER_THIN);// 上边框
		cs.setBorderRight(CellStyle.BORDER_THIN);// 右边框
		cs.setAlignment(CellStyle.ALIGN_CENTER); // 居中
		return cs;
	}

	public static void main(String[] args) {

		String path = Thread.currentThread().getContextClassLoader().getResource("").toString();
		String path1 = ExportExcelUtils_DataList_Mysql.class.getResource("/").getPath();
		System.out.println(path);
		System.out.println(path1);
	}

}
